#!/bin/bash

# 导入视图到目标服务器脚本
# 目标服务器: 47.116.179.170
# 数据库: order

echo "=========================================="
echo "生产管理系统视图导入脚本"
echo "目标服务器: 47.116.179.170"
echo "目标数据库: order"
echo "=========================================="

# 目标服务器配置
TARGET_HOST="47.116.179.170"
TARGET_USER="order"
TARGET_PASS="2R1P1i1F6xGBkAYd"
TARGET_DB="order"

# 检查视图文件是否存在
if [ ! -f "views_for_order_database.sql" ]; then
    echo "错误: views_for_order_database.sql 文件不存在!"
    exit 1
fi

echo "开始导入视图到目标服务器..."

# 测试连接
echo "测试数据库连接..."
mysql -h "$TARGET_HOST" -u "$TARGET_USER" -p"$TARGET_PASS" -e "SELECT 1;" 2>/dev/null
if [ $? -eq 0 ]; then
    echo "✓ 数据库连接成功"
else
    echo "✗ 数据库连接失败，请检查服务器地址和密码"
    exit 1
fi

# 检查目标数据库是否存在
echo "检查目标数据库..."
mysql -h "$TARGET_HOST" -u "$TARGET_USER" -p"$TARGET_PASS" -e "USE $TARGET_DB;" 2>/dev/null
if [ $? -eq 0 ]; then
    echo "✓ 目标数据库 $TARGET_DB 存在"
else
    echo "✗ 目标数据库 $TARGET_DB 不存在"
    echo "请先在目标服务器上创建数据库: CREATE DATABASE order;"
    exit 1
fi

# 检查必要的表是否存在
echo "检查必要的表..."
REQUIRED_TABLES=("fa_scanwork_report" "fa_scanwork_employee" "fa_scanwork_allocation" "fa_scanwork_order" "fa_scanwork_production_plan")

for table in "${REQUIRED_TABLES[@]}"; do
    mysql -h "$TARGET_HOST" -u "$TARGET_USER" -p"$TARGET_PASS" "$TARGET_DB" -e "DESCRIBE $table;" 2>/dev/null
    if [ $? -eq 0 ]; then
        echo "✓ 表 $table 存在"
    else
        echo "✗ 表 $table 不存在"
        echo "请确保所有必要的表已经导入"
        exit 1
    fi
done

# 导入视图
echo "导入视图..."
mysql -h "$TARGET_HOST" -u "$TARGET_USER" -p"$TARGET_PASS" "$TARGET_DB" < views_for_order_database.sql

if [ $? -eq 0 ]; then
    echo "✓ 视图导入成功!"
    
    # 验证视图是否创建成功
    echo "验证视图..."
    mysql -h "$TARGET_HOST" -u "$TARGET_USER" -p"$TARGET_PASS" "$TARGET_DB" -e "
    SELECT TABLE_NAME, TABLE_TYPE 
    FROM information_schema.TABLES 
    WHERE TABLE_SCHEMA = '$TARGET_DB' 
    AND TABLE_NAME IN ('v_quality_statistics', 'v_employee_workload', 'v_production_progress');"
    
    echo ""
    echo "=========================================="
    echo "导入完成!"
    echo "已创建的视图:"
    echo "- v_quality_statistics (质量统计)"
    echo "- v_employee_workload (员工工作量)"
    echo "- v_production_progress (生产进度)"
    echo "=========================================="
else
    echo "✗ 视图导入失败，请检查错误信息"
    exit 1
fi
